import os
import rarfile
from openpyxl import Workbook
from openpyxl.styles import NamedStyle
from openpyxl.styles import PatternFill, Font
import tkinter as tk
from tkinter import filedialog
script_path = os.path.abspath(__file__)

# 获取脚本所在目录的路径
script_dir = os.path.dirname(script_path)

# 切换到脚本所在目录
os.chdir(script_dir)

# 打印当前工作目录，确认目录已切换
print("当前工作目录:", os.getcwd())
print('***********',os.getcwd())
rarfile.UNRAR_TOOL = './UnRAR.exe'

def get_value(file):
    data=[['Ship Date','Tier1','Customer PO','Internal PO','Qty','Start ICCID','End ICCID','Start MSISDN','End MSISDN','Remarks','EXW','Date'],]
    #data=[]

    # 打开RAR文件
    with rarfile.RarFile(file) as rf:
        # 遍历RAR文件中的所有文件
        for fileinfo in rf.infolist():
            # 检查文件扩展名是否为.txt
            if fileinfo.filename.endswith('.txt'):
                ship_date=fileinfo.filename.split('_')[-1][:-4]
                tier1='_'.join(fileinfo.filename.split('/')[1].split('_')[:-3])
                internal_po=fileinfo.filename.split('_')[-3]
                qty=int(fileinfo.filename.split('_')[-2][:-3])
                
                # 读取文件内容
                with rf.open(fileinfo.filename) as f:
                    content = f.read().decode('utf-8')
                    #print(f"文件名: {fileinfo.filename}")
                    #print(f"文件内容:\n{content}")
                    #print("-" * 40)  # 分隔线
                    start_iccid=str(content.split('\n')[0].split(' ')[0])
                    end_iccid=content.split('\n')[-2].split(' ')[0]

                    start_msisdn=content.split('\n')[0].split(' ')[1]
                    end_msisdn=content.split('\n')[-2].split(' ')[1]
                    #print(content.split('\n')[0],content.split('\n')[-2])
                data.append([ship_date,tier1,'',internal_po,qty,start_iccid,end_iccid,start_msisdn,end_msisdn])
    return data
                #print(ship_date,tier1,internal_po,qty,start_iccid,end_iccid,start_msisdn,end_msisdn)






# 创建一个Tkinter窗口
root = tk.Tk()
root.withdraw()  # 隐藏主窗口

# 打开文件选择对话框
file_path = filedialog.askopenfilename(title="选择一个文件")

# 显示选择的文件路径
if file_path:
    print("选择的文件是:", file_path)
    data=get_value(file_path)
    # 创建工作簿和工作表
    wb = Workbook()
    ws = wb.active

    # 创建一个文本格式的样式
    #text_style = NamedStyle(name='text', number_format='@')
    text_style = NamedStyle(name='integer', number_format='0')
    #print(data)
    # 写入数据到工作表
    for row in data:
        ws.append(row)

    # 应用文本格式到长数字所在的列
    #for cell in ws['E']:  # 假设长数字在第二列
    #    cell.style = text_style
    for col in range(1, 13):  # A列的索引是1
        cell = ws.cell(row=1, column=col)   # 获取单元格
        cell.fill = PatternFill(start_color="D3D3D3", end_color="D3D3D3", fill_type="solid")  # 设置灰色背景
        cell.font = Font(bold=True) 
    ws.column_dimensions['A'].width = 15

    # 设置多个列宽
    ws.column_dimensions['B'].width = 15
    ws.column_dimensions['C'].width = 15
    ws.column_dimensions['D'].width = 15
    ws.column_dimensions['E'].width = 15
    ws.column_dimensions['F'].width = 25
    ws.column_dimensions['G'].width = 25
    ws.column_dimensions['H'].width = 15
    ws.column_dimensions['I'].width = 15
    #ws.set_column('A:I', 20)
    # 保存工作簿
    wb.save('data.xlsx')

# 销毁Tkinter窗口
root.destroy()
os.startfile('data.xlsx')
